Hello all and welcome back to the Excel Tip of the Week! This week, we are examining how to use Power Query to automatically pull in the correct charge-out rates for a selection of employees, even as those rates change over time. This is a familiar enough accounting example to use, but the underlying technique can be used for any similar case with multiple IDs and dates, for example exchange rates or purchase prices.
Setting up the problem
We’re going to examine a case where we have the following export of hours worked on a project by various staff:
We then have a master table showing the charge-out rates for each individual, and the dates that rate applies to:
Note that we have both “from” and “to” dates for the historic rates, but only “from” dates for the current ones. We want to fill in the appropriate charge-out rate for each individual based on the dates that the work occurred, and we want a solution that will be easily forwards-compatible as we add both more work and update the rates going forward.
We start by loading both of these tables into Power Query, using Data => Get & Transform. They are called WorkDone and ChargeOutRates respectively. Note that both are formatted as Excel Tables (see TOTW #163) for easy adding of more rows later on.
We need to connect the two tables together – to do this, we are going to duplicate our WorkDone query, and then run a Merge with the ChargeOutRates query:
Note that we are pairing both the ID and Date column from the WorkDone column, with the ID and From column in ChargeOutRates. We have chosen a Full Outer join, which will give us all the rows from both tables – which is important as not all the “from” dates will necessarily appear in the WorkDone table, and we need to make sure that we capture all of them.
Getting the output we want
The merge leaves us with a table of values next to each row of the original WorkDone table; we expand the ID, From, and Rate columns to get the following result:
As you can see, we have a lot of null values where the date or ID appears in only one table or the other. We need to fix this, which we will do by combining the two into one. We start with the dates:
That formula:
= if [Date] is null then [ChargeOutRates.From] else [Date]
This will use the WorkDone Date column if it is available, or if note use the From date in the ChargeOutRates table. We then remove the old incomplete columns and repeat the whole process to also create a merged ID column. Finally we sort the data into order according to the dates and IDs. That gives us this:
We now have complete Date and ID columns, with values on every row, but gaps in the Hours column (where we have data from the ChargeOutRates table on days no work was done) and in the Rate column (for dates in the WorkDone table which don’t line up with the From column of ChargeOutRates). We can use Fill Down to complete the Rate column, then filter the Hours column to remove the blanks. We finish with a little cosmetic work, reordering and renaming columns and making sure the types are correct:
From there we can load the completed table back into Excel, ready for working with or putting into a PivotTable. And any future new work or changes in rates are easily incorporated with just a simple refresh.
You can see the data, the queries, and try it out for yourself in the attached file.
Find out more about grouping in TOTW #286.
Excel community
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.
